import sqlite3
from os import path


##@brief all things to do with db are using this class
class DBConnector():

	##Constructor
	#connect to database or create when it is not exists
	#@param filename file name to be used to database
	def __init__(self, filename):
		##@var _conn
		#database connection
		if not path.isfile(path.realpath(filename)):
			self._conn = sqlite3.connect(filename)
			self.makeDB()
		else:
			self._conn = sqlite3.connect(filename)
	
	##Destructor
	#close connection
	def __del__(self):
		self._conn.close()
	
	def close(self):
		self._conn.commit()
		self._conn.close()
	
	##make db for first use
	def makeDB(self):
		pass

class MainDBConnector(DBConnector):
	def __init__(self, filename):
		DBConnector.__init__(self, filename)
	
	##Implementation
	#make consumer, users table
	def makeDB(self):
		self._conn.execute('create table consumer('+\
				'consumer_key text unique not null,'+\
				'consumer_secret text not null)')
		self._conn.execute('create table users('+\
				'screenname text unique,'+\
				'userid int unique not null,'+\
				'access_token text unique not null,'+\
				'access_secret text not null,'+\
				'failed_count int not null default 0)')
		self._conn.execute('create table last_mention('+\
				'userid int unique not null,'+\
				'mentionid int not null)')
	
	##get Consumer key and secret
	#@return a tuple that contains 'consumer_key' and 'consumer_secret'
	def getConsumer(self):
		cur = self._conn.execute('select consumer_key, consumer_secret from consumer')
		return cur.fetchone()

	##get user's access tokens
	#@return list of tuple that contains 'access_token' and 'access_secret'
	def getAccessTokens(self):
		cur = self._conn.execute('select access_token, access_secret from users')
		return cur.fetchall()

	##insert user
	#@param access_token access_token
	#@param access_secret access_secret
	def insertUser(self, access_token, access_secret, userid, screenname=None):
		if self._conn.execute('select count(*) from users where userid=%d'%userid).fetchone()[0] > 0:
			self._conn.execute("update users set access_token='%s', access_secret='%s' where userid=%d"%(access_token, access_secret, userid))
		else:
			self._conn.execute("insert into users(access_token, access_secret, userid) values('%s','%s',%d)"%(access_token, access_secret, userid))

		if screenname is not None:
			self._conn.execute("update users set screenname='%s' where userid=%d"%(screenname, userid))

	def getLastMention(self, userid):
		return self._conn.execute('select mentionid from last_mention where userid=%d'%userid).fetchone()[0]
	def setLastMention(self, userid, mentionid):
		self._conn.execute('update last_mention set mentionid=%d where userid=%d'%(mentionid, userid))
		self._conn.commit()
